package cn.conac.rc.ofs.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.conac.rc.framework.repository.GenericDao;
import cn.conac.rc.ofs.entity.DutyEntity;

@Repository
public interface DutyRepository extends GenericDao<DutyEntity, Integer> {
	
	/**
	 * 根据baseId查询该机构的所有职责信息(并按照职责顺序号升序排序)
	 * @param baseId
	 * @return DutyEntity的List
	 */
	public List<DutyEntity> findByBaseIdOrderByDutyNumberAsc(Integer baseId);
	
	
	/**
	 * 根据departId查询该内设机构对应的的所有职责信息(并按照职责顺序号升序排序)
	 * @param departId 内设机构Id
	 * @return DutyEntity的List
	 */
	@Query(value = "select d.* from ofs_depart_duty dd, ofs_duty d where dd.duty_id=d.id and dd.depart_id = :departId order by d.duty_number asc ", nativeQuery = true)
	public List<DutyEntity> findByDepatId(@Param("departId") Integer departId);

	/**
	 * 根据部门库Id获取没有关联内设的职能职责
	 * @return
	 */
	@Query(value = "select distinct od.* from ofs_organization oo left join ofs_organization_base oob on oo.BASE_ID = oob.id left join ofs_org_status oos on oos.id = oob.id inner join ofs_duty od on oo.BASE_ID = od.org_id left join ofs_depart_duty odd on od.ID = odd.duty_id where odd.depart_id is null and oob.own_sys is not null and oo.BASE_ID is not null and oob.area_id is not null and oos.status = '03' and oos.update_type <> '50' and oos.is_history = 0 and oo.id = :id order by od.id ", nativeQuery = true)
	List<DutyEntity> findDutyWithNoDutyListByOrgId(@Param("id") Integer id);

	/**
	 * 根据baseId查询该机构的职责信息对应的最大的DutyNumber
	 * @param baseId
	 * @return Integer
	 */
	@Query(value = "select nvl(max(od.duty_number),0) from ofs_duty od where od.org_id = :baseId ", nativeQuery = true)
	public int findMaxDutyNumberByOrgId(@Param("baseId") Integer baseId);
	
	
	/**
	 * 根据baseID删除内设机构信息
	 * @param baseId
	 * @return void
	 */
	@Query(value = "delete  from ofs_duty du where du.org_id= :baseId", nativeQuery = true)
	@Modifying
	void deleteByBaseId(@Param("baseId") Integer baseId);
	
	//▼▼▼▼▼▼▼▼权责用DAO▼▼▼▼▼▼▼▼
	@Query(value = "select duty.* from ofs_duty duty where duty.org_id = :orgId order by duty.duty_number asc", nativeQuery = true)
	public List<DutyEntity> findDutyBy(@Param("orgId") String orgId);

	@Query(value = "select duty.* from ofs_duty duty where duty.org_id = :orgId and duty.duty_code = :code", nativeQuery = true)
	public List<DutyEntity> findByCode(@Param("code") String code, @Param("orgId") Integer orgId);

	@Query(value = "select distinct bean.* from ael_items_duty ct left join ofs_duty bean on ct.duty_code=bean.duty_code where ct.item_id=:itemId and bean.org_id=:orgId", nativeQuery = true)
	public List<DutyEntity> findByItemIdAndOrgId(@Param("itemId") Integer itemId, @Param("orgId") Integer orgId);
	//▲▲▲▲▲▲▲▲权责用DAO▲▲▲▲▲▲▲▲
}
